Задаци - групне функције и спајање табела¶

Сви задаци који следе могу да се провежбају и у систему SQL Server Management Studio.

Упити се пишу и покрећу када се кликне New Query након што се покрене систем SQL Server и кликне на креирану базу Pesme у прозору Object Explorer. Фајл са упитима SQLQuery1.sql може, али и не мора да се сачува.

Након што се унесе једна команда, кликне се на дугме Execute. Уколико се у простору за писање команди налази више њих, потребно је обележити ону коју желимо да покренемо. Ако има више база података, обавезно проверити да ли је поред овог дугмета назив базе у којој желите да вршите упите.

Сви наредни SELECT упити обрађују податке из базе података фиктивне компаније за продају музичких композиција (углавном песама). Следи списак свих табела са колонама. Примарни кључеви су истакнути болдом, а страни италиком.

За сваког извођача приказати укупан број снимљених минута, заокружен на две децимале.

SELECT izvodjac.naziv, round(SUM(kompozicija.trajanje) / (1000.0 * 60.0), 2) AS minuti
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album
JOIN izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac

2. За сваког извођача приказати број композиција снимљених у MPEG формату. Занемарити оне извођаче који имају мање од пет таквих композиција.

SELECT izvodjac.naziv, round(SUM(kompozicija.trajanje) / (1000.0 * 60.0), 2) AS minuti
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album JOIN
        izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac

За сваки жанр приказати назив жанра и просечно трајање композиције у секундама (уредити их опадајуће, по трајању).

SELECT izvodjac.naziv, round(SUM(kompozicija.trajanje) / (1000.0 * 60.0), 2) AS minuti
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album JOIN
        izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac

Приказати укупну дужину свих композиција групе Metallica.

SELECT SUM(trajanje) AS ukupno_trajanje
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album JOIN
        izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
WHERE izvodjac.naziv = 'Metallica'

Приказати извођаче којима је просечна дужина трајања композиције између три и четири минута.

SELECT izvodjac.naziv, round(AVG(trajanje / (1000.0 * 60.0)), 2) AS prosecno_minuta
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album JOIN
        izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac
HAVING prosecno_minuta BETWEEN 3.0 AND 4.0

За сваког уметника/групу који има пет или више албума приказати број албума (резултат приказати сортирано по броју албума, опадајуће)

SELECT izvodjac.naziv, COUNT(*) AS broj_albuma
FROM izvodjac JOIN
        album ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac
HAVING broj_albuma >= 5
ORDER BY broj_albuma DESC

7. За сваког уметника/групу који има пет или више албума приказати број албума (резултат приказати сортирано по броју албума, опадајуће)

SELECT izvodjac.naziv, count(DISTINCT kompozicija.id_zanr) AS broj_zanrova
FROM kompozicija JOIN
        album ON kompozicija.id_album = album.id_album JOIN
        izvodjac ON izvodjac.id_izvodjac = album.id_izvodjac
GROUP BY izvodjac.id_izvodjac
HAVING broj_zanrova > 1
ORDER BY broj_zanrova DESC

8. За сваког извођача приказати идентификатор, име и укупан број рок композиција које је снимио (ако није снимио ниједну, приказати нулу).

Пошто се тражи приказ броја композиција за све извођаче, а многи извођачи нису снимили ниједну рок композицију, потребно је да употребимо лево спајање.

SELECT i.naziv, COUNT(k.naziv) AS broj_rok_kompozicija
FROM (izvodjac i JOIN
        album a ON a.id_izvodjac = i.id_izvodjac)
LEFT JOIN
        (kompozicija k JOIN
        zanr z ON k.id_zanr = z.id_zanr AND z.naziv = 'Rock') ON a.id_album = k.id_album
GROUP BY i.id_izvodjac
ORDER BY broj_rok_kompozicija DESC

За сваки жанр приказати дужину најкраће и најдуже композиције.

10. Приказати број ставки на свакој наруџбеници испорученој у Бразил (приказати идентификатор наруџбенице, име и презиме купца и број ставки). Резултате сортирати неопадајуће по броју ставки.

11. Приказати имена, презимена и укупне износе наруџбина (заокружене на 2 децимале) сваког купца за 3 купаца који су направили највеће износе наруџбина. Резултат приказати опадајуће по укупном износу.

12. За сваког запосленог прикази идентификатор, име, презиме, број запослених којима је он надређени службеник. Приказати и оне запослене којима је тај број једнак нули.

Претходна лекција Следећа лекција

Слова